const pool = require('../../config/database')


class models{
    async findAllJob(){
        const [rows] = await pool.query('SELECT * FROM companies c LEFT JOIN jobs j ON j.company_id = c.company_id ORDER BY j.id DESC');
        return rows;
    }

    // async findAllJob(data){
    //     const { page=1, size=4 } = data;
    //     const [rows, [[{total}]]] = await pool.Promise.all([
    //         pool.query('SELECT * FROM jobs j LEFT JOIN companies c ON j.company_id = c.company_id ORDER BY j.id DESC LIMIT ?,?', [(page - 1) * size, +size]),
    //         pool.query('SELECT COUNT(*) AS total FROM jobs LEFT JOIN companies')
    //     ]);
    //     return rows;
    // }

    async findJob(data){
        const { page=1, size=4 } = data;
        const [rows,  [[{ total }]] ] = await Promise.all([
            pool.query('SELECT * FROM jobs LIMIT ?,?', [(page - 1) * size, +size]),
            pool.query('SELECT COUNT(*) AS total FROM jobs')
            ])
        return({
            code: 0,
            data: {
            list: rows[0],
            total
            }
        })
    }


    async finById(id){
        const [row] = await pool.query(
            // 'SELECT * FROM jobs WHERE id= ?',[id]
            'SELECT *FROM companies c LEFT JOIN jobs j ON c.company_id = j.company_id LEFT JOIN ( SELECT company_id, name AS adname, sex FROM company_admins)ad ON j.company_id = ad.company_id WHERE j.id = ?',[id]
            // 'SELECT companies c LEFT JOIN jobs j ON c.company_id = j.company_id LEFT JOIN ( SELECT company_id, name AS adname, sex FROM company_admins)ad ON j.company_id = ad.company_id WHERE j.id = ?',[id]
        );
        return row;
    }


    async createJob(jobData){
        
        const { company_id,post,area,salary,work_experience,education,job_attribute,welfare,label,is_urgent } = jobData
        const welfareStr = Array.isArray(welfare) ? JSON.stringify(welfare) : welfare;
        const labelStr   = Array.isArray(label)   ? label.join(',')   : label;
        const [result] = await pool.query(
            'INSERT INTO jobs (company_id,post,area,salary,work_experience,education,job_attribute,welfare,label,is_urgent) VALUES (?,?,?,?,?,?,?,?,?,?)',
                              [ company_id,post,area,salary,work_experience,education,job_attribute,welfareStr,labelStr,is_urgent ]
        );
        return { id: result.insertId,...jobData };
    }

    async updateJob(id,jobData){
        const { post,area,salary,work_experience,education,job_attribute,welfare,label,is_urgent } = jobData;
        const welfareStr = Array.isArray(welfare) ? JSON.stringify(welfare) : welfare;
        const [result] = await pool.query(
            'UPDATE jobs SET post=?, area=?, salary=?, work_experience=?, education=?, job_attribute=?, welfare=?, label=?, is_urgent=? WHERE id= ?',[post,area,salary,work_experience,education,job_attribute,welfareStr,label,is_urgent,id]
        );
        return result.affectedRows >0;
    }

    async deleteJob(id){
        const [result] = await pool.query(
            'DELETE FROM jobs WHERE id=?',[id]
        );
        return result.affectedRows >0;
    }

    async  filtrateJobs(filters) {
        let sql = 'SELECT * FROM jobs WHERE 1=1';
        const params = [];

        if(filters.post) {
            sql += ' AND post LIKE ?';
            params.push(`%${filters.post}%`);
        }

        if (filters.area) {
            sql += ' AND area LIKE ?';
            params.push(`%${filters.area}%`);
        }

        if (filters.education) {
            sql += ' AND education =?';
            params.push(filters.education)
        }

        sql += ' ORDER BY id DESC'
        const [rows] = await pool.execute(sql,params);
        return rows;
    }

}


module.exports = new models();